﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Daycare.Models;

namespace Daycare.DAL
{
    public class FamilyRepository
    {
        public FamilyRepository()
        {

        }

        public Family GetFamily(int childId)
        {
            DataTable tempTable = new DataTable();

            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    SqlDataAdapter sda = new SqlDataAdapter();

                    sda.SelectCommand = new SqlCommand("daycare.GetFamily", con);
                    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                    sda.SelectCommand.Parameters.AddWithValue("@ChildId", childId);
                    sda.Fill(tempTable);
                }
            }
            catch (SqlException)
            {
                return null;
            }

            if (tempTable.Rows.Count > 0)
            {
                Family family = new Family();

                family.familyId = Int32.Parse(tempTable.Rows[0]["FamilyId"].ToString());
                family.isActive = Boolean.Parse(tempTable.Rows[0]["IsActive"].ToString());
                family.enforceApprovedPickupList = Boolean.Parse(tempTable.Rows[0]["EnforceApprovedPickupList"].ToString());

                return family;
            }
            else
            {
                return null;
            }
        }

        public List<Child> GetAllChildren(bool excludeInactiveFamilies)
        {
            DataTable tempTable = new DataTable();

            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    SqlDataAdapter sda = new SqlDataAdapter();

                    sda.SelectCommand = new SqlCommand("daycare.GetAllChildren", con);
                    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                    sda.SelectCommand.Parameters.AddWithValue("@ExcludeInactiveFamilies", excludeInactiveFamilies);
                    sda.Fill(tempTable);
                }
            }
            catch (SqlException)
            {
                return null;
            }

            if (tempTable.Rows.Count > 0)
            {
                children = new List<Child>();

                foreach (DataRow row in tempTable.Rows)
                {
                    Child child = new Child();
                    child.firstName = row["FirstName"].ToString();
                    child.lastName = row["LastName"].ToString();
                    child.nickName = row["NickName"].ToString();
                    child.isActive = Boolean.Parse(row["IsActive"].ToString());
                    child.childId = Int32.Parse(row["ChildId"].ToString());
                    child.dateOfBirth = DateTime.Parse(row["DateOfBirth"].ToString());

                    children.Add(child);
                }

                return children;
            }
            else
            {
                return null;
            }
        }

        public List<Child> GetFamilyChildren(int selectedChildId)
        {
            DataTable tempTable = new DataTable();

            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    SqlDataAdapter sda = new SqlDataAdapter();

                    sda.SelectCommand = new SqlCommand("daycare.GetFamilyChildren", con);
                    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                    sda.SelectCommand.Parameters.AddWithValue("@ChildId", selectedChildId);
                    sda.Fill(tempTable);
                }
            }
            catch (SqlException)
            {
                return null;
            }

            if (tempTable.Rows.Count > 0)
            {
                children = new List<Child>();

                foreach (DataRow row in tempTable.Rows)
                {
                    Child child = new Child();
                    child.firstName = row["FirstName"].ToString();
                    child.lastName = row["LastName"].ToString();
                    child.nickName = row["NickName"].ToString();
                    child.isActive = Boolean.Parse(row["IsActive"].ToString());
                    child.childId = Int32.Parse(row["ChildId"].ToString());
                    child.dateOfBirth = DateTime.Parse(row["DateOfBirth"].ToString());

                    children.Add(child);
                }

                return children;
            }
            else
            {
                return null;
            }
        }

        public List<Contact> GetFamilyContacts(int selectedChildId)
        {
            DataTable tempTable = new DataTable();

            try
            {
                using (SqlConnection con = new SqlConnection(Configuration.ConnectionString))
                {
                    SqlDataAdapter sda = new SqlDataAdapter();

                    sda.SelectCommand = new SqlCommand("daycare.GetFamilyContacts", con);
                    sda.SelectCommand.CommandType = CommandType.StoredProcedure;
                    sda.SelectCommand.Parameters.AddWithValue("@ChildId", selectedChildId);
                    sda.Fill(tempTable);
                }
            }
            catch (SqlException)
            {
                return null;
            }

            if (tempTable.Rows.Count > 0)
            {
                contacts = new List<Contact>();

                foreach (DataRow row in tempTable.Rows)
                {
                    Contact contact = new Contact();
                    contact.firstName = row["FirstName"].ToString();
                    contact.lastName = row["LastName"].ToString();
                    contact.contactId = Int32.Parse(row["contactId"].ToString());
                    contact.fullName = row["FullName"].ToString();
                    contact.emailAddress = row["EmailAddress"].ToString();
                    contact.phoneNumber = row["PhoneNumber"].ToString();
                    contact.isApprovedPickup = Boolean.Parse(row["IsApprovedPickup"].ToString());
                    contact.isEmergencyContact = Boolean.Parse(row["IsEmergencyContact"].ToString());

                    contacts.Add(contact);
                }

                return contacts;
            }
            else
            {
                return null;
            }
        }

        List<Contact> contacts;
        List<Child> children;
    }
}